﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Xml.Linq;

namespace XMLTest
{
    class Program
    {
        /// <summary>
        /// 将DataTable转化成实体类模型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> ConvertToList<T>(DataTable dt) where T : new()
        {
            // 定义集合 
            List<T> ts = new List<T>();

            // 获得此模型的类型 
            Type type = typeof(T);
            //定义一个临时变量 
            string tempName = string.Empty;
            //遍历DataTable中所有的数据行  
            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();
                // 获得此模型的公共属性 
                PropertyInfo[] propertys = t.GetType().GetProperties();
                //遍历该对象的所有属性 
                foreach (PropertyInfo pi in propertys)
                {
                    tempName = pi.Name;//将属性名称赋值给临时变量   
                    //检查DataTable是否包含此列（列名==对象的属性名）     
                    if (dt.Columns.Contains(tempName))
                    {
                        // 判断此属性是否有Setter   
                        if (!pi.CanWrite) continue;//该属性不可写，直接跳出   
                        //取值   
                        object value = dr[tempName];
                        //如果非空，则赋给对象的属性   
                        if (value != DBNull.Value)
                            pi.SetValue(t, value, null);
                    }
                }
                //对象添加到泛型集合中 
                ts.Add(t);
            }
            return ts;
        }

        static IEnumerable<XAttribute> GetAttrbute(DataRow tableRow)
        {
            string[] nameArray = new[] { "ID", "Name", "Age", "Gender" };
            for (int i = 0; i < 4; i++)
            {
                yield return new XAttribute(nameArray[i], tableRow[i]);
            }
        }
        static string ConvertToXMLFromTable(List<Student> stuList)
        {
            return GX.XmlUtil.Serializer(typeof(List<Student>), stuList);
        }

        static void Main(string[] args)
        {
            string connstr = "server=127.0.0.1;database=Student;uid=sa;pwd=123456";
            string sql = " select * from [Student].[dbo].[T_Students]";
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                DataTable dt = ds.Tables[0];

                Console.WriteLine("*****************************************************************************");
                Console.WriteLine("数据库到DataTable到Xml");
                var stuList = ConvertToList<Student>(dt);
                foreach (var stu in stuList)
                {
                    Console.WriteLine("id:" + stu.Id + ",name:" + stu.Name + ",age:" + stu.Age + ",genger:" + stu.Gender);
                };

                //将Student的数据集合写入到xml
                var stuListStr = ConvertToXMLFromTable(stuList);
                Console.WriteLine(stuListStr);

                //实体类对象转化到xml
                Student stu1 = new Student() { Id = 101, Name = "丁小未", Age = 24, Gender = true };
                string xml = GX.XmlUtil.Serializer(typeof(Student), stu1);
                Console.WriteLine("*****************************************************************************");
                Console.WriteLine("xml转化成实体对象");
                Console.WriteLine(xml);

                //xml转化成实体对象
                Student stu2 = GX.XmlUtil.Deserialize(typeof(Student), xml) as Student;
                Console.WriteLine("*****************************************************************************");
                Console.WriteLine("xml转化成实体对象");
                Console.WriteLine("id:" + stu2.Id + ",name:" + stu2.Name + ",age:" + stu2.Age + ",genger:" + stu2.Gender);

                //DataTable转化成Xml
                DataTable dt1 = new DataTable("StudentTable");
                //添加列属性
                dt1.Columns.Add("Id", typeof(Int64));
                dt1.Columns.Add("Name", typeof(string));
                dt1.Columns.Add("Age", typeof(int));
                dt1.Columns.Add("Gender", typeof(bool));

                //添加行
                dt1.Rows.Add(1, "丁小未", 25, true);
                dt1.Rows.Add(2, "倪莹莹", 25, false);
                Console.WriteLine("*****************************************************************************");
                Console.WriteLine("DataTable对象转化成xml");
                xml = GX.XmlUtil.Serializer(typeof(DataTable), dt1);

                //xml转化到DataTable
                DataTable dt2 = GX.XmlUtil.Deserialize(typeof(DataTable), xml) as DataTable;
                Console.WriteLine("*****************************************************************************");
                Console.WriteLine("xml转化成DataTable");
                foreach (DataRow dr in dt2.Rows)
                {
                    foreach (DataColumn col in dt2.Columns)
                    {
                        Console.Write(dr[col].ToString() + " ");
                    }

                    Console.Write("\r\n");
                }

                //List转化到xml
                List<Student> list1 = new List<Student>();
                list1.Add(new Student() { Id = 101, Name = "丁小未", Age = 24, Gender = true });
                list1.Add(new Student() { Id = 101, Name = "倪莹莹", Age = 24, Gender = false });
                Console.WriteLine("*****************************************************************************");
                Console.WriteLine("List转化成DataTable");
                xml = GX.XmlUtil.Serializer(typeof(List<Student>), list1);
                Console.WriteLine(xml);

                //Xml转化到List
                List<Student> list2 = GX.XmlUtil.Deserialize(typeof(List<Student>), xml) as List<Student>;
                Console.WriteLine("*****************************************************************************");
                Console.WriteLine("Xml转化到List");
                foreach (Student stu in list2)
                {
                    Console.WriteLine(stu.Name + "," + stu.Age.ToString());
                }
            }
            Console.Read();
        }
    }
}

